#write in 2019,6,2
#write by lizhuoying

import random

from sqlalchemy import create_engine,Column,Integer,String,func,ForeignKey

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship,backref


DB_URI='mysql+pymysql://root@127.0.0.1:3306/test?charset=utf8'

engine=create_engine(DB_URI)

Base=declarative_base(engine)#元类编程，将对象转化成元类

class Book(Base):
    __tablename__='book'
    id=Column(Integer,primary_key=True,autoincrement=True)
    title=Column(String(20))
    price=Column(Integer)
    auth_id=Column(Integer,ForeignKey("author.id"))
    auth=relationship("Author",backref='book',cascade="")
    def __repr__(self):
        return '<Book name:%s>'%self.title

class Author(Base):
    __tablename__='author'
    id=Column(Integer,primary_key=True,autoincrement=True)
    name=Column(String(20))


Base.metadata.drop_all()
Base.metadata.create_all()

auth=Author(name='李大宝')
book=Book(title='hello',price=45)
book.auth=auth

session=sessionmaker(engine)()
session.add(book)
session.commit()

book1=session.query(Book).all()
print(book1)
auth1=session.query(Author).all()
print(auth1)